package com.apobates.jforum.grief.schema2doc.reactive.dialect;

import com.apobates.jforum.grief.schema2doc.core.SchemaTableStrategy;
import com.apobates.jforum.grief.schema2doc.core.entity.Column;
import com.apobates.jforum.grief.schema2doc.core.entity.Nullable;
import com.apobates.jforum.grief.schema2doc.core.entity.PrimayKey;
import com.apobates.jforum.grief.schema2doc.core.entity.Table;
import com.apobates.jforum.grief.schema2doc.core.schema.SchemaDialect;
import com.apobates.jforum.grief.schema2doc.reactive.schema.AbstractRxJavaQueryExecutor;
import io.reactivex.Flowable;
import io.reactivex.Single;
import org.davidmoten.rx.jdbc.Database;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
import java.util.Optional;

/**
 * RxJava2的SQLServer的结果集供应商
 * https://github.com/davidmoten/rxjava-jdbc
 */
public class RxJavaSQLServerExecutor extends AbstractRxJavaQueryExecutor {
    private final static Logger logger = LoggerFactory.getLogger(RxJavaSQLServerExecutor.class);

    protected RxJavaSQLServerExecutor(Database db, SchemaDialect dialect) {
        super(db, dialect);
    }

    @Override
    protected Flowable<Table> getTables(Database db, Optional<String> dbName, Optional<String> schema, SchemaTableStrategy tableStrategy) throws IllegalArgumentException {
        // select * from ESB_pub_client.INFORMATION_SCHEMA.TABLES
        /*
        SELECT a.name AS tableName, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS remarks
            FROM sys.tables a LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0)
            WHERE CHARINDEX('JB_', a.name) = 1;
        * SELECT a.name AS tableName, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS remarks FROM sys.tables a LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0)
        * */
        String sql = "SELECT a.object_id, a.name AS tableName, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS remarks FROM sys.tables a LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0)";
        if(tableStrategy.sql(SchemaDialect.SQLServer).isPresent()){
            sql+= " WHERE " + tableStrategy.sql(SchemaDialect.SQLServer).get().replaceAll(SchemaTableStrategy.placeholder, "a.name");
        }
        logger.debug("[RxJava-SQLServer-TABLE]execute sql statement::"+sql);
        return db.select(sql).get(record -> {
            // String dbName, String tableName, String remarks
            return Table.noSchema(
                    record.getInt("object_id"),
                    dbName.get(),
                    record.getString("tableName"),
                    record.getString("remarks"));
        }).filter(table->tableStrategy.filter().test(table.getTableName()));
    }

    @Override
    protected Single<List<Column>> getColumns(Database db, Table table) {
        /* @basic: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
        * SELECT
COLUMN_NAME AS columnName,
IS_NULLABLE as nullable,
DATA_TYPE as typeName,
NUMERIC_SCALE AS decimalDigits,
COLUMN_DEFAULT AS columnDef,
CHARACTER_OCTET_LENGTH as columnSize,
CHARACTER_MAXIMUM_LENGTH
FROM ESB_pub_client.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'JB_CLML';
-------------------------------------------------

SELECT B.name AS columnName, B.max_length as columnSize, B.scale AS decimalDigits, B.precision, B.is_nullable AS nullable, T.name AS typeName, C.value AS remarks, D.definition AS columnDef
　　FROM sys.tables A　INNER JOIN sys.columns B ON B.object_id = A.object_id INNER JOIN sys.types T ON B.system_type_id = T.system_type_id LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id LEFT JOIN sys.default_constraints D ON B.object_id = D.parent_object_id AND B.column_id = D.parent_column_id
　　WHERE A.name = :tablename
        * */
        final List<String> pks = getPrimayKey(db, table.getTableName());
        String sql = "SELECT B.name AS columnName, B.max_length as columnSize, B.scale AS decimalDigits, B.precision, B.is_nullable AS nullable, T.name AS typeName, C.value AS remarks, D.definition AS columnDef " +
                "FROM sys.tables A " +
                "INNER JOIN sys.columns B ON B.object_id = A.object_id " +
                "INNER JOIN sys.types T ON B.system_type_id = T.system_type_id " +
                "LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id " +
                "LEFT JOIN sys.default_constraints D ON B.object_id = D.parent_object_id AND B.column_id = D.parent_column_id " +
                "WHERE A.name = :tablename";
        logger.debug("[RxJava-SQLServer-COLUMN]execute sql statement::"+sql);
        // Fetch results using jOOQ
        return db.select(sql).parameter("tablename", table.getTableName()).get(record -> {
            // Basic:: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
            String columnName = record.getString("columnName");
            return Column.basic(
                            columnName,
                            record.getString("typeName"),
                            record.getString("columnSize"),
                            Nullable.of(record.getInt("nullable")),
                            record.getString("remarks"))
                    .toFull(
                            record.getString("decimalDigits"),
                            pks.contains(columnName)? PrimayKey.YES:PrimayKey.NO,
                            record.getString("columnDef")); // String decimalDigits, PrimayKey pk, String columnDef
        }).toList();
    }

    private List<String> getPrimayKey(Database db, String tableName){
        // SELECT COLUMN_NAME  FROM  ESB_pub_client.INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_NAME= 'JB_CLML'
        String sql = "SELECT COLUMN_NAME AS columnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = :TN ";
        return db.select(sql).parameter("TN", tableName).get(record -> {
            return record.getString("columnName");
        }).toList().blockingGet();

    }
}
